﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ContactsDBConnection$" formatter="bizobjlist of Contact(addresses(country))">
	<Sql>
    SELECT
    c.id AS id,lastName,firstName,middleName,phone,mobile,email,isUser,c.companyId, c.dateCreated, c.dateUpdated
    ,avitarImage, customTitle, dateOfBirth, gender, location
    ,ad.id AS addressesId,street1 as addressesStreet1,street2 as addressesStreet2,street3 as addressesStreet3
    ,city as addressesCity,stateProvince as addressesStateProvince,zipCode as addressesZipCode,contactId as addressesContactId
    ,countries.code as countryregionCode,countries.englishName as countryEnglishName
    ,ct.id as type_id, ct.name as type_name, ct.mapImageName as type_mapImageName, ct.cacheName as type_cacheName
    ,latitude as addresseslatitude,longitude as addresseslongitude
    ,at.id as addressestype_id,at.mapImageName as addressestype_mapImageName,at.currentMapImageName as addressestype_currentMapImageName
    ,at.cacheName as addressestype_cacheName,a.baseTitle as addressestype_baseTitle

    FROM Contacts c
    LEFT JOIN ContactType ct on ct.id = c.typeId
    LEFT JOIN ADDRESSES ad on ad.contactId = c.id
    LEFT join countries on countries.code  = ad.countryCode
    LEFT OUTER JOIN AddressTypes AS at on at.id = ad.typeId
    LEFT OUTER JOIN Articles AS a on a.id = at.id
    WHERE
    c.companyId=@companyId
    <code><![CDATA[
		bool hasCriteria = false;
		string firstPerc = "", secondPerc = "", cx="x";
		//-------- Fitering ---------------------------------------------------		
		if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
		{
			sql.Append(" AND (");
			foreach(IBizObj filOp in criteria.get("filterOptions") as IBizObjList)
			{
				cx = filOp.get("criteria") as string;
				if (string.Compare(cx, "s", true) == 0)
					firstPerc = "%";
				else if (string.Compare(cx, "e", true) == 0)
					secondPerc = "%";
				else if (string.Compare(cx, "c", true) == 0)
					firstPerc = secondPerc = "%";
				if (firstPerc.Length > 0 || secondPerc.Length > 0)
					cx = "like";
				if(filOp.get("field") != null && (string)filOp.get("field") != "")
				{
					hasCriteria = true;
					sql.AppendFormat(" {0} {1} '{3}{2}{4}'", filOp.get("field"), cx, filOp.get("value"), firstPerc, secondPerc);
					sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
				}
			}
			if(hasCriteria)
			{
				sql.Length -= 4;
				sql.Append(") ");
			}
			else
				sql.Length -= 6;
		}
		//-------- Ordering ---------------------------------------------------		
		if(criteria != null && ((IBizObjList)criteria.get("sortOptions")).numElements != 0)
		{
			StringBuilder sortInfo = new StringBuilder(512);
			foreach(IBizObj sortOp in criteria.get("sortOptions") as IBizObjList)
			{
				if(sortInfo.Length > 0)
					sortInfo.Append(",");
				sortInfo.Append(sortOp.get("field"));
				if(Cmn.IsFalse(sortOp.get("isAsc")))
					sortInfo.Append(" DESC");
			}
			sql.Append("order by ").Append(sortInfo);
		}
	]]></code>
	</Sql>
	<Param name="companyId" type="UniqueIdentifier" />
</AdoNetDataAccess>